/**
 * Repositorio endereco com os principais metodos
 * 
 * @author			Paulo Lima
 * @corporation 	TeckSoft 2014
 * @project 		Araujo Seguros
 * @version 		1.0 
 * @since			Release 1  05/03/2014
 */
package repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import models.Endereco;
import util.ConnectionFactory;

public class RepositorioEndereco implements IRepositorioEndereco {

	// ---------------------------------------------------------------------------------
	public boolean inserirEndereco(Endereco endereco) throws SQLException {
		String sql = "insert into endereco values (?,?,?,?,?,?,?)";

		try {

			Connection conIntranet = new ConnectionFactory()
					.getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			stm1.setInt(1, endereco.getId());
			stm1.setString(2, endereco.getEndereco());
			stm1.setString(3, endereco.getComplemento());
			stm1.setString(4, endereco.getEstado());
			stm1.setString(5, endereco.getCidade());
			stm1.setString(6, endereco.getBairro());
			stm1.setString(7, endereco.getCEP());
			stm1.execute();
			stm1.close();
			conIntranet.close();
			return true;

		} catch (Exception e) {

			return false;
		}

	}

	// ---------------------------------------------------------------------------------
	public boolean removerEndereco(Endereco endereco) throws SQLException {
		String sql = "DELETE FROM endereco WHERE id=?";

		try {

			Connection conIntranet = new ConnectionFactory()
					.getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			stm1.setInt(1, endereco.getId());
			stm1.execute();
			stm1.close();
			conIntranet.close();
			return true;

		} catch (Exception e) {
			return false;
		}

	}

	// ---------------------------------------------------------------------------------
	public boolean atualizarEndereco(Endereco endereco) throws SQLException {
		String sql = "UPDATE endereco SET "
				+ "endereco=?, complemento=?, estado=?, cidade=?, bairro=?, CEP=? WHERE id=?";

		try {

			Connection conIntranet = new ConnectionFactory()
					.getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			stm1.setString(1, endereco.getEndereco());
			stm1.setString(2, endereco.getComplemento());
			stm1.setString(3, endereco.getEstado());
			stm1.setString(4, endereco.getCidade());
			stm1.setString(5, endereco.getBairro());
			stm1.setString(6, endereco.getCEP());
			stm1.setInt(7, endereco.getId());
			stm1.executeUpdate();
			stm1.close();
			conIntranet.close();
			return true;

		} catch (Exception e) {
			return false;
		}

	}

	// ---------------------------------------------------------------------------------
	public List<Endereco> listarEndereco() throws SQLException {

		ArrayList<Endereco> enderecoDB = new ArrayList<>();
		Endereco endereco;
		String sql = "SELECT * FROM endereco";

		try {
			Connection conIntranet = new ConnectionFactory()
					.getConnection();
			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);
			ResultSet rs1 = stm1.executeQuery();

			while (rs1.next()) {

				endereco = new Endereco();
				endereco.setId(rs1.getInt(1));
				endereco.setEndereco(rs1.getString(2));
				endereco.setComplemento(rs1.getString(3));
				endereco.setEstado(rs1.getString(4));
				endereco.setCidade(rs1.getString(5));
				endereco.setBairro(rs1.getString(6));
				endereco.setCEP(rs1.getString(7));
				enderecoDB.add(endereco);

			}
			rs1.close();
			stm1.close();
			conIntranet.close();
		} catch (Exception e) {
			new SQLException();
		}

		return enderecoDB;
	}

	// ---------------------------------------------------------------------------------
	public List<Endereco> buscarEndereco(String chave) throws SQLException {

		ArrayList<Endereco> enderecoDB = new ArrayList<>();
		Endereco endereco;
		String sql = "SELECT * FROM endereco WHERE endereco LIKE '%" + chave
				+ "%'";

		try {
			Connection conIntranet = new ConnectionFactory()
					.getConnection();
			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);
			ResultSet rs1 = stm1.executeQuery();

			while (rs1.next()) {

				endereco = new Endereco();
				endereco.setId(rs1.getInt(1));
				endereco.setEndereco(rs1.getString(2));
				endereco.setComplemento(rs1.getString(3));
				endereco.setEstado(rs1.getString(4));
				endereco.setCidade(rs1.getString(5));
				endereco.setBairro(rs1.getString(6));
				endereco.setCEP(rs1.getString(7));
				enderecoDB.add(endereco);

			}
			rs1.close();
			stm1.close();
			conIntranet.close();
		} catch (Exception e) {

			new SQLException();
		}

		return enderecoDB;
	}

	// ---------------------------------------------------------------------------------
	public Endereco retornaEndereco(int id) throws SQLException {
		Endereco endereco;
		String sql = "SELECT * FROM endereco WHERE id = " + id + "";

		try {
			Connection conIntranet = new ConnectionFactory()
					.getConnection();
			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);
			ResultSet rs1 = stm1.executeQuery();

			while (rs1.next()) {

				endereco = new Endereco();
				endereco.setId(rs1.getInt(1));
				endereco.setEndereco(rs1.getString(2));
				endereco.setComplemento(rs1.getString(3));
				endereco.setEstado(rs1.getString(4));
				endereco.setCidade(rs1.getString(5));
				endereco.setBairro(rs1.getString(6));
				endereco.setCEP(rs1.getString(7));
				return endereco;

			}
			rs1.close();
			stm1.close();
			conIntranet.close();

		} catch (Exception e) {

			new SQLException();
		}

		return null;
	}
	// ---------------------------------------------------------------------------------
}
